﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Twi.COMMON.Core;
using System.Data.Common;
using System.Data;
using Twi.DataAccess.OutInterface;
using System.Xml.Linq;

namespace Twi.DataAccess.Common
{
    public class DACommon : IDACommon
    {
        public DACommon(DbUtility db, string mappingTableName = "TTableMapping")
        {
            DB = db;
            _DBProviderType = db._DbProviderType;
            _MappingTableName = mappingTableName;
            //默认SQLMap
            SQLMapFullPath = TwiPathHelper.MapPath("~/SQLMap/TwiSQLMap.xml");
            //默认TableMapping
            TableMappingFullPath = TwiPathHelper.MapPath("~/SQLMap/TableMapping.config");
        }

        /// <summary>
        /// 通用数据库访问类实例
        /// </summary>
        private DbUtility DB
        {
            get;
            set;
        }
        /// <summary>
        /// 数据库类型
        /// </summary>
        private DbProviderType _DBProviderType
        {
            get;
            set;
        }
        /// <summary>
        /// 参数前缀@ ：?
        /// </summary>
        private string _ParameterPrefix
        {
            get
            {
                if (_DBProviderType == DbProviderType.Oracle || _DBProviderType == DbProviderType.OracleDataAccess)
                {
                    return ":";
                }
                else
                {
                    return "@";
                }
            }
        }

        /// <summary>
        /// SQL-MAP 配置文件的完整路径
        /// </summary>
        public string SQLMapFullPath
        {
            get;
            set;
        }

        /// <summary>
        /// TableMapping 配置文件的完整路径
        /// </summary>
        public string TableMappingFullPath
        {
            get;
            set;
        }

        /// <summary>
        /// 最大记录编号存储信息
        /// </summary>
        public DBMaxCode MaxCode
        {
            get;
            set;
        }

        /// <summary>
        /// 表映射存储的表名
        /// </summary>
        private string _MappingTableName
        {
            get;
            set;
        }

        private DataTable _TableMappingData
        {
            get;
            set;
        }
        /// <summary>
        /// 根据映射名获取表名
        /// </summary>
        /// <param name="mappingName"></param>
        /// <param name="isTMConfig">是否使用TableMapping.config的配置</param>
        /// <returns></returns>
        public string GetTableName(string mappingName,bool isTMConfig=false)
        {
            return GetTableName(mappingName, isTMConfig, null);
            //if (isTMConfig) //使用TableMapping.config
            //{
            //    TwiPathHelper.ExistFile(TableMappingFullPath, true, "未能找到TableMapping配置文件");
            //    return TableMappingHelper.GetByCode(TableMappingFullPath, mappingName);
            //}
            //else 
            //{
            //    //为空表示不映射
            //    if (string.IsNullOrEmpty(_MappingTableName))
            //    {
            //        return mappingName;
            //    }
            //    if (_TableMappingData == null || _TableMappingData.Rows.Count == 0)
            //    {
            //        string strSql = string.Format("select * from {0}", _MappingTableName);
            //        _TableMappingData = DB.ExecuteDataTable(strSql);
            //    }
            //    var rows = _TableMappingData.Select(string.Format("FMAPPINGNAME='{0}'", mappingName));
            //    if (rows.Count() != 1)
            //    {
            //        rows = _TableMappingData.Select(string.Format("FTABLENAME='{0}'", mappingName));
            //        if (rows.Count() == 0)
            //        {
            //            throw new Exception("TableMapping查找失败，未配置【"+mappingName+"】TableMapping数据。");
            //        }
            //        else if (rows.Count() > 1)
            //        {
            //            throw new Exception("TableMapping不明确，配置了多个【" + mappingName + "】的TableMapping。");
            //        }
            //    }
            //    return "" + rows[0]["FTABLENAME"];
            //}
        }

        /// <summary>
        /// 根据映射名获取表名
        /// </summary>
        /// <param name="mappingName"></param>
        /// <param name="isTMConfig">是否使用TableMapping.config的配置</param>
        /// <param name="tmFileFullName">TableMapping文件名+路径，NullOrDefault为默认配置值</param>
        /// <returns></returns>
        public string GetTableName(string mappingName, bool isTMConfig , string tmFileFullName) 
        {            
            if (isTMConfig) //使用TableMapping.config
            {
                tmFileFullName = tmFileFullName.SelfOrDefault(TableMappingFullPath);
                TwiPathHelper.ExistFile(TableMappingFullPath, true, "未能找到TableMapping配置文件");
                return TableMappingHelper.GetByCode(tmFileFullName, mappingName, _DBProviderType);
            }
            else
            {
                //为空表示不映射
                if (string.IsNullOrEmpty(_MappingTableName))
                {
                    return mappingName;
                }
                if (_TableMappingData == null || _TableMappingData.Rows.Count == 0)
                {
                    string strSql = string.Format("select * from {0}", _MappingTableName);
                    _TableMappingData = DB.ExecuteDataTable(strSql);
                }
                var rows = _TableMappingData.Select(string.Format("FMAPPINGNAME='{0}'", mappingName));
                if (rows.Count() != 1)
                {
                    rows = _TableMappingData.Select(string.Format("FTABLENAME='{0}'", mappingName));
                    if (rows.Count() == 0)
                    {
                        throw new Exception("TableMapping查找失败，未配置【" + mappingName + "】TableMapping数据。");
                    }
                    else if (rows.Count() > 1)
                    {
                        throw new Exception("TableMapping不明确，配置了多个【" + mappingName + "】的TableMapping。");
                    }
                }
                return "" + rows[0]["FTABLENAME"];
            }
        }

        /// <summary>
        /// 转化DataTable列名为大写【会智能判断是否需要转换，如ORACLE数据库就不需要转换了】
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public DataTable ColNameToUpper(DataTable dt)
        {
            if (_DBProviderType != DbProviderType.Oracle && _DBProviderType != DbProviderType.OracleDataAccess)
            {
                dt = TwiJsonHelper.ColNameToUpper(dt);
            }
            return dt;
        }

        /// <summary>
        /// 字段重复性判断
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="fieldName">字段名称：（如：FName）</param>
        /// <param name="fieldValue">字段值</param>
        /// <returns>是否重复</returns>
        public bool ExistField(string tableName, string fieldName, string fieldValue)
        {
            //string strSql = string.Format("select count(1) from {0} where {1}='{2}'", tableName, field, fieldValue);
            //return DB.Exists(strSql);
            tableName = GetTableName(tableName);
            //“表名”或者“字段名”是不允许用变量代替,所以只能直接传了
            string strSql = string.Format("select count(1) from {0} where {1}={2}fieldValue", tableName, fieldName, _ParameterPrefix);
            List<DbParameter> listParas = new List<DbParameter> {
                DB.CreateDbParameter("fieldValue", fieldValue),
            };
            return DB.Exists(strSql, listParas);

        }

        /// <summary>
        /// 字段重复性判断(用于编辑)
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="fieldName">字段名称：（如：FName）</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="id">编辑记录的ID</param>
        /// <param name="idField">ID字段名（默认为FID）</param>
        /// <returns>是否重复</returns>
        public bool ExistField(string tableName, string fieldName, string fieldValue, string id, string idField = "FID")
        {
            //string strSql = string.Format("select count(1) from {0} where {1}='{2}' and FID !='{3}'", tableName, fieldName, fieldValue, id);
            //return DB.Exists(strSql);
            tableName = GetTableName(tableName);
            string strSql = string.Format("select count(1) from {0} where {1}={2}fieldValue and {3}!={2}id", tableName, fieldName, _ParameterPrefix, idField);
            List<DbParameter> listParas = new List<DbParameter>{
                DB.CreateDbParameter("fieldValue",fieldValue),
                DB.CreateDbParameter("id",id)
            };
            return DB.Exists(strSql, listParas);
        }

        /// <summary>
        /// 字段重复性通用判断
        /// [ID为空时表示新增前判断，有值为新增后或编辑时判断]
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="fieldName">字段名称：（如：FName）</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="id">记录的ID</param>
        /// <param name="idField">ID字段名（默认为FID）</param>
        /// <returns>是否重复</returns>
        public bool ExistFieldCommon(string tableName, string fieldName, string fieldValue, string id = null, string idField = "FID")
        {
            tableName = GetTableName(tableName);
            //“表名”或者“字段名”是不允许用变量代替,所以只能直接传了
            string strSql = "";
            List<DbParameter> listParas = null;
            if (string.IsNullOrEmpty(id)) //新增前
            {
                strSql = string.Format("select count(1) from {0} where {1}={2}fieldValue", tableName, fieldName, _ParameterPrefix);
                listParas = new List<DbParameter> {
                DB.CreateDbParameter("fieldValue", fieldValue)};
            }
            else  //新增后或编辑
            {
                strSql = string.Format("select count(1) from {0} where {1}={2}fieldValue and {3}!={2}id", tableName, fieldName, _ParameterPrefix, idField);
                listParas = new List<DbParameter>{
                DB.CreateDbParameter("fieldValue",fieldValue),
                DB.CreateDbParameter("id",id)};
            }
            return DB.Exists(strSql, listParas);
        }



        /// <summary>
        /// Sort重复性判断
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="sort">排序值</param>
        /// <param name="sortField">排序字段(default:FSort)</param>
        /// <returns></returns>
        public bool ExistSort(string tableName, int? sort, string sortField = "FSort")
        {
            tableName = GetTableName(tableName);
            //string strSql = string.Format("select FSort from {0} where FSort = {1}", tableName, sort);
            //return DB.Exists(strSql);

            string strSql = string.Format("select count(1) from {0} where {1} = {2}sort", tableName, sortField, _ParameterPrefix);
            List<DbParameter> listParas = new List<DbParameter>{
                DB.CreateDbParameter("sort",sort)
            };
            return DB.Exists(strSql, listParas);
        }

        /// <summary>
        /// Sort重复性判断(用于编辑)
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="sort">排序值</param>
        /// <param name="id">编辑记录的ID</param>
        /// <param name="sortField">排序字段(default:FSort)</param>
        /// <param name="idField">ID字段(default:FID)</param>
        /// <returns></returns>
        public bool ExistEditSort(string tableName, int? sort, string id, string sortField = "FSort", string idField = "FID")
        {
            tableName = GetTableName(tableName);
            //string strSql = string.Format("select FSort from {0} where FSort = {1} and FID !='{2}'", tableName, sort, id);
            //return DB.Exists(strSql);
            string strSql = string.Format("select count(1) from {0} where {1}={2}sort and {3} !={2}id", tableName, sortField, _ParameterPrefix, idField);
            List<DbParameter> listParas = new List<DbParameter>{
                DB.CreateDbParameter("sort",sort),
                DB.CreateDbParameter("id",id)
            };
            return DB.Exists(strSql, listParas);
        }

        /// <summary>
        /// Sort重复性判断(带字段筛选)
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="sort">排序值</param>        
        /// <param name="filterField">筛选字段</param>
        /// <param name="filterFieldValue">筛选字段值</param>
        /// <param name="sortField">排序字段(default:FSort)</param>
        /// <returns></returns>
        public bool ExistSort(string tableName, int? sort, string filterField, string filterFieldValue, string sortField = "FSort")
        {
            tableName = GetTableName(tableName);
            //string strSql = string.Format("select FSort from {0} where FSort = {1} and {2}='{3}'", tableName, sort, filterField, filterFieldValue);
            //return DB.Exists(strSql);
            string strSql = string.Format("select count(1) from {0} where {1}={2}sort and {3}={2}filterFieldValue", tableName, sortField, _ParameterPrefix, filterField);
            List<DbParameter> listParas = new List<DbParameter>{
                DB.CreateDbParameter("sort",sort),
                DB.CreateDbParameter("filterFieldValue",filterFieldValue)
            };
            return DB.Exists(strSql, listParas);
        }

        /// <summary>
        /// Sort重复性判断(用于编辑)(带字段筛选)
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="sort">排序</param>
        /// <param name="id">编辑记录的ID</param>
        /// <param name="filterField">筛选字段</param>
        /// <param name="filterFieldValue">筛选字段值</param>
        /// <param name="sortField">排序字段(default:FSort)</param>
        /// <param name="idField">ID字段(default:FID)</param>
        /// <returns></returns>
        public bool ExistEditSort(string tableName, int? sort, string id, string filterField, string filterFieldValue, string sortField = "FSort", string idField = "FID")
        {
            tableName = GetTableName(tableName);
            //string strSql = string.Format("select FSort from {0} where FSort = {1} and FID !='{2}' and {3}='{4}'", tableName, sort, id, filterField, filterFieldValue);
            //return DB.Exists(strSql);

            string strSql = string.Format("select count(1) from {0} where {1}={2}sort and {3}={2}filterFieldValue and {4}!={2}id", tableName, sortField, _ParameterPrefix, filterField, idField);
            List<DbParameter> listParas = new List<DbParameter>{
                DB.CreateDbParameter("sort",sort),
                DB.CreateDbParameter("filterFieldValue",filterFieldValue),
                DB.CreateDbParameter("id",id)
            };
            return DB.Exists(strSql, listParas);
        }

        /// <summary>
        /// Sort重复性通用判断
        /// </summary>
        /// <returns></returns>
        public bool ExistSortCommon(SortModel model)
        {
            model.TableName = GetTableName(model.TableName);
            string strSql = "";
            List<DbParameter> listParas = null;
            if (model.NewOrEdit == "New" && string.IsNullOrEmpty(model.IDField))  //新增且没指定当前ID，一般为新增前判断。
            {
                if (model.FilterField == "") //全表排序
                {
                    strSql = string.Format("select count(1) from {0} where {1} = {2}sort", model.TableName, model.SortField, _ParameterPrefix);
                    listParas = new List<DbParameter>{
                    DB.CreateDbParameter("sort",model.SortValue)};
                }
                else  //按某个字段排序
                {
                    strSql = string.Format("select count(1) from {0} where {1}={2}sort and {3}={2}filterFieldValue", model.TableName, model.SortField, _ParameterPrefix, model.FilterField);
                    listParas = new List<DbParameter>{
                    DB.CreateDbParameter("sort",model.SortValue),
                    DB.CreateDbParameter("filterFieldValue",model.FilterValue)};
                }
            }
            else  //新增或编辑后判断
            {
                if (model.FilterField == "") //全表排序
                {
                    strSql = string.Format("select count(1) from {0} where {1}={2}sort and {3} !={2}id", model.TableName, model.SortField, _ParameterPrefix, model.IDField);
                    listParas = new List<DbParameter>{
                    DB.CreateDbParameter("sort",model.SortValue),
                    DB.CreateDbParameter("id",model.IDValue) };
                }
                else  //按某个字段排序
                {
                    strSql = string.Format("select count(1) from {0} where {1}={2}sort and {3}={2}filterFieldValue and {4}!={2}id", model.TableName, model.SortField, _ParameterPrefix, model.FilterField, model.IDField);
                    listParas = new List<DbParameter>{
                    DB.CreateDbParameter("sort",model.SortValue),
                    DB.CreateDbParameter("filterFieldValue",model.FilterValue),
                    DB.CreateDbParameter("id",model.IDValue)};
                }
            }

            return DB.Exists(strSql, listParas);
        }

        /// <summary>
        /// 获取当前最大Sort
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="sortField">排序字段(default:FSort)</param>
        /// <returns></returns>
        public int GetMaxSort(string tableName, string sortField = "FSort")
        {
            tableName = GetTableName(tableName);
            string strSql = string.Format("select Max({0}) from {1}", sortField, tableName);
            object obj = DB.ExecuteScalar(strSql);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        /// <summary>
        /// 获取当前最大Sort(带字段筛选)
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="filterField">筛选字段</param>
        /// <param name="filterFieldValue">筛选字段值</param>
        /// <param name="sortField">排序字段(default:FSort)</param>
        /// <returns></returns>
        public int GetMaxSort(string tableName, string filterField, string filterFieldValue, string sortField = "FSort")
        {
            tableName = GetTableName(tableName);
            string strSql = string.Format("select Max({0}) from {1} where {2}={3}filterField", sortField, tableName, filterField, _ParameterPrefix);
            List<DbParameter> listParas = new List<DbParameter>{
                DB.CreateDbParameter("filterField",filterFieldValue)
            };
            object obj = DB.ExecuteScalar(strSql, listParas);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        /// <summary>
        /// 获取当前最大Sort(通用)
        /// [filterField为空时表示获取全表最大，有值为根据某个字段获取最大]
        /// </summary>
        /// <param name="tableName">表名称（如：TUsers）</param>
        /// <param name="filterField">筛选字段</param>
        /// <param name="filterFieldValue">筛选字段值</param>
        /// <param name="sortField">排序字段(default:FSort)</param>
        /// <returns></returns>
        public int GetMaxSortCommon(string tableName, string sortField = "FSort", string filterField = null, string filterFieldValue = null)
        {
            tableName = GetTableName(tableName);
            object obj = null;
            if (string.IsNullOrEmpty(filterField))
            {
                string strSql = string.Format("select Max({0}) from {1}", sortField, tableName);
                obj = DB.ExecuteScalar(strSql);
            }
            else
            {
                if (string.IsNullOrEmpty(filterFieldValue))
                {
                    string strSql = string.Format("select Max({0}) from {1} where {2} is null", sortField, tableName, filterField);
                    obj = DB.ExecuteScalar(strSql);
                }
                else
                {
                    string strSql = string.Format("select Max({0}) from {1} where {2}={3}filterField", sortField, tableName, filterField, _ParameterPrefix);
                    List<DbParameter> listParas = new List<DbParameter>{
                    DB.CreateDbParameter("filterField",filterFieldValue)};
                    obj = DB.ExecuteScalar(strSql, listParas);
                }
            }

            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        /// <summary>
        /// 新增或编辑后智能生成排序
        /// </summary>
        /// <param name="model"></param>
        public void AutoGenSort(SortModel model)
        {
            model.TableName = GetTableName(model.TableName);
            if (string.IsNullOrEmpty(model.SortValue)) //新增或编辑时没填排序
            {
                int maxSort = GetMaxSortCommon(model.TableName, model.SortField, model.FilterField, model.FilterValue);
                string strSql = string.Format("update {0} set {1}={2}{1} where {3}={2}{3}", model.TableName, model.SortField, _ParameterPrefix, model.IDField);
                List<DbParameter> listParas = new List<DbParameter>{
                    DB.CreateDbParameter(model.SortField,maxSort+1),
                    DB.CreateDbParameter(model.IDField,model.IDValue)};
                //if (!string.IsNullOrEmpty(model.FilterField)) //带筛选字段
                //{
                //    strSql += string.Format(" and {0}={1}{0}",model.FilterField,_ParameterPrefix);
                //    listParas.Add(DB.CreateDbParameter(model.FilterField, model.FilterValue));
                //}
                DB.ExecuteNonQuery(strSql, listParas);

            }
            else //新增或编辑时填了排序
            {
                bool isExist = ExistFieldCommon(model.TableName, model.SortField, model.SortValue, model.IDValue, model.IDField);
                if (isExist) //如果存在给定的排序
                {
                    List<TransMultiPara> listTmp = new List<TransMultiPara>();
                    string strSql = string.Format("update {0} set {1}={1}+1 where {2}!={3}{2} and {1}>={3}sortValue", model.TableName, model.SortField, model.IDField, _ParameterPrefix);
                    List<DbParameter> listParas = new List<DbParameter>()
                    {
                        DB.CreateDbParameter(model.IDField,model.IDValue),
                        DB.CreateDbParameter("sortValue",model.SortValue)
                    };

                    //if (!string.IsNullOrEmpty(model.FilterField)) //带筛选字段
                    //{
                    //    strSql += string.Format(" and {0}={1}{0}", model.FilterField, _ParameterPrefix);
                    //    listParas.Add(DB.CreateDbParameter(model.FilterField, model.FilterValue));

                    //}
                    DB.ExecuteNonQuery(strSql, listParas);
                }
            }
        }


        /// <summary>
        /// 根据条件查询个数
        /// </summary>
        /// <param name="data">json对象</param>
        /// <example>
        /// json编辑格式：
        /// {
        ///     TableName:"TDuty",
        ///     Condition:{
        ///         FID:""
        ///     }
        /// }
        /// </example>
        /// <returns></returns>
        public int GetCountByCondition(string data)
        {
            CheckSafe.Validate(data);
            var dict = JSSerialize.Deserialize<dynamic>(data);
            string tableName = "" + dict["TableName"];
            tableName = GetTableName(tableName);
            var dictCondition = dict["Condition"];
            return GetCountByCondition(tableName, dictCondition);
        }

        /// <summary>
        /// 根据条件查询个数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dictConditionFields">简单条件字典（所谓简单，就是条件都是 = 和 and 连接）</param>
        public int GetCountByCondition(string tableName, Dictionary<string, object> dictConditionFields)
        {
            string conditionFields = "";
            foreach (string key in dictConditionFields.Keys)
            {
                conditionFields += " and " + string.Format("{0}={1}{0}", key, _ParameterPrefix);
            }
            conditionFields = conditionFields.Substring(4); //去掉最前面的一个and
            string strSql = string.Format("select count(1) from {0} where {1}", tableName, conditionFields);
            var dbParams = DB.CreateDbParameter(dictConditionFields, "");
            object obj = DB.ExecuteScalar(strSql, dbParams);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 简单新增操作
        /// </summary>
        /// <param name="data">json对象</param>
        /// json新增格式：
        /// {
        ///     TableName:"TDuty",
        ///     IDField:'FID',
        ///     Fields:{
        ///         FName:"开发人员",
        ///         FSort:"8",
        ///         FID:"SEQ_User.nextval",
        ///         FDate:"to_date('2013-11-12','yyyy-mm-dd')"
        ///     },
        ///     FnFields:'FID,FDate',
        ///     MaxCodeFields:'FID,FCode',
        ///     DateFields:'FCreateTime',
        ///     Expressions:'FIP,FNAME'
        /// }
        /// <remarks>
        ///    FnFields 为采用数据库函数取值字段配置集合,
        ///    MaxCodeFields 为从最大记录编号表中取值的字段集合
        ///    DateFields:日期字段
        ///    Expressions:表达式集合
        /// </remarks>
        /// <returns>新增记录的ID</returns>
        public string SimpleNew(string data)
        {
            CheckSafe.Validate(data);
            var dict = JSSerialize.Deserialize<dynamic>(data);
            string tableName = "" + dict["TableName"];
            tableName = GetTableName(tableName);
            var dictFields = dict["Fields"];
            string idfield = "FID";
            if (dict.ContainsKey("IDField"))
            {
                idfield = "" + dict["IDField"];
            }
            if (!dictFields.ContainsKey(idfield))
            {
                dictFields.Add(idfield, "" + Guid.NewGuid());
            }
            else
            {
                dictFields[idfield] = dictFields[idfield] == "" ? "" + Guid.NewGuid() : dictFields[idfield];
            }
            Dictionary<string, string> dictFnFields = null;
            if (dict.ContainsKey("FnFields"))
            {
                string[] fnFields = ("" + dict["FnFields"]).Split(',');
                dictFnFields = new Dictionary<string, string>();
                for (int i = 0; i < fnFields.Length; i++)
                {
                    dictFnFields.Add(fnFields[i], "" + dictFields[fnFields[i]]);
                    dictFields.Remove(fnFields[i]);
                }
            }
            Dictionary<string, string> dictMaxCodeFields = null;
            if (dict.ContainsKey("MaxCodeFields"))
            {
                string[] maxCodeFields = ("" + dict["MaxCodeFields"]).Split(',');
                dictMaxCodeFields = new Dictionary<string, string>();
                for (int i = 0; i < maxCodeFields.Length; i++)
                {
                    dictMaxCodeFields.Add(maxCodeFields[i], "" + dictFields[maxCodeFields[i]]);
                    dictFields.Remove(maxCodeFields[i]);
                }
            }
            if (dict.ContainsKey("DateFields"))
            {
                string[] dateFields = dict["DateFields"].Split(',');
                foreach (string field in dateFields) {
                    dictFields[field] = Convert.ToDateTime(dictFields[field]);
                }
            }
            //表达式值替换
            if (dict.ContainsKey("Expressions"))
            {
                string[] expressions = dict["Expressions"].Split(',');                
                foreach (string expressionField in expressions)
                {
                    if (dictFields.ContainsKey(expressionField))
                    {
                        string expressionCode = "" + dictFields[expressionField];
                        var expVal = TwiExpression.GetExpressionValueByCode(expressionCode);
                        dictFields[expressionField] = expVal == null ? "" : expVal;
                    }
                }
            }
            string nextMaxCode = SimpleNew(tableName, dictFields, dictFnFields, dictMaxCodeFields);
            string returnFID = nextMaxCode == null ? "" + dictFields[idfield] : nextMaxCode;
            return returnFID;
        }

        /// <summary>
        /// 简单新增操作（老:很多参数，新：组合成一个参数）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dictFields">字段及其值字典</param>
        /// <param name="fnFields">FnFields 为采用数据库函数取值字段配置集合</param>
        /// <param name="dictMaxCodeFields">为从最大记录编号表中取值的字段集合</param>
        /// <returns>新增记录的ID</returns>
        public string SimpleNew(string tableName, Dictionary<string, object> dictFields, Dictionary<string, string> dictFnFields = null, Dictionary<string, string> dictMaxCodeFields = null)
        {
            Dictionary<string, dynamic> varDict = new Dictionary<string, dynamic>();
            varDict.Add("tableName", tableName);
            varDict.Add("dictFields", dictFields);
            varDict.Add("dictFnFields", dictFnFields);
            varDict.Add("dictMaxCodeFields", dictMaxCodeFields);
            return SimpleNew(varDict);
        }

        /// <summary>
        /// 简单新增操作（新：组合成一个参数）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dictFields">字段及其值字典</param>
        /// <param name="fnFields">FnFields 为采用数据库函数取值字段配置集合</param>
        /// <param name="dictMaxCodeFields">为从最大记录编号表中取值的字段集合</param>
        /// <param name="expressions">表达式集合</param>
        /// <returns>新增记录的ID</returns>
        /// 之前的参数较多：string tableName, Dictionary<string, object> dictFields, Dictionary<string, string> dictFnFields = null, Dictionary<string, string> dictMaxCodeFields = null)
        public string SimpleNew(Dictionary<string, dynamic> varDict)
        {
            /*参数*/
            string tableName = varDict["tableName"]; //表名
            Dictionary<string, object> dictFields = varDict["dictFields"]; //字段及其值字典
            Dictionary<string, string> dictFnFields = varDict.SelfOrDefault("dictFnFields", null); //FnFields 为采用数据库函数取值字段配置集合
            Dictionary<string, string> dictMaxCodeFields = varDict.SelfOrDefault("dictMaxCodeFields", null); //为从最大记录编号表中取值的字段集合
            
            string fields = TwiDictionaryHelper<string>.GetDictionaryKeys(dictFields, ",");
            string fieldParams = _ParameterPrefix + TwiDictionaryHelper<string>.GetDictionaryKeys(dictFields, "," + _ParameterPrefix);
            string fnFields = "";
            string fnFieldValues = "";
            if (dictFnFields != null && dictFnFields.Count > 0)
            {
                foreach (KeyValuePair<string, string> kvp in dictFnFields)
                {
                    fnFields += "," + kvp.Key;
                    fnFieldValues += "," + kvp.Value;
                }
                fields = fields.Length == 0 ? fnFields.Substring(1) : fields + fnFields;
                fieldParams = fieldParams.Length == 0 ? fnFieldValues.Substring(1) : fieldParams + fnFieldValues;
            }
            string maxCodeFields = "";
            string maxCodeFieldValues = "";
            int nextMaxCode = 0;
            if (dictMaxCodeFields != null && dictMaxCodeFields.Count > 0)
            {
                foreach (KeyValuePair<string, string> kvp in dictMaxCodeFields)
                {
                    maxCodeFields += "," + kvp.Key;
                    nextMaxCode = MaxCode.GetNextValue(kvp.Value);
                    maxCodeFieldValues += string.Format(",'{0}'", nextMaxCode);
                }
                fields = fields.Length == 0 ? maxCodeFields.Substring(1) : fields + maxCodeFields;
                fieldParams = fieldParams.Length == 0 ? maxCodeFieldValues.Substring(1) : fieldParams + maxCodeFieldValues;
            }
            string strSql = string.Format("insert into {0} ({1}) values ({2})", tableName, fields, fieldParams);
            var dbParams = DB.CreateDbParameter(dictFields);
            DB.ExecuteNonQuery(strSql, dbParams);
            return nextMaxCode > 0 ? "" + nextMaxCode : null;
        }

        /// <summary>
        /// 简单编辑操作
        /// </summary>
        /// <param name="data">json对象</param>
        /// <example>
        /// json编辑格式：
        /// {
        ///     TableName:"TDUTY",
        ///     Fields:{
        ///         FName:"开发人员",
        ///         FSort:"8"
        ///     },
        ///     Condition:{
        ///         FID:""
        ///     },
        ///     FnFields:'FID,FDATE',
        ///     DateFields:'FLASTEDITTIME',
        ///     Expressions:'FIP,FLASTEDITTIME'
        /// }
        /// </example>
        /// <remarks>
        ///    FnFields 为采用数据库函数取值字段配置集合
        ///    DateFields:日期字段
        ///    Expressions:表达式集合
        /// </remarks>
        public void SimpleEdit(string data)
        {
            CheckSafe.Validate(data);
            var dict = JSSerialize.Deserialize<dynamic>(data);
            string tableName = "" + dict["TableName"];
            tableName = GetTableName(tableName);
            var dictFields = dict["Fields"];
            var dictCondition = dict["Condition"];

            Dictionary<string, string> dictFnFields = null;
            if (dict.ContainsKey("FnFields"))
            {
                string[] fnFields = ("" + dict["FnFields"]).Split(',');
                dictFnFields = new Dictionary<string, string>();
                for (int i = 0; i < fnFields.Length; i++)
                {
                    dictFnFields.Add(fnFields[i], "" + dictFields[fnFields[i]]);
                    dictFields.Remove(fnFields[i]);
                }
            }
            if (dict.ContainsKey("DateFields"))
            {
                string[] dateFields = dict["DateFields"].Split(',');
                foreach (string field in dateFields)
                {
                    dictFields[field] = Convert.ToDateTime(dictFields[field]);
                }
            }
            //表达式值替换
            if (dict.ContainsKey("Expressions"))
            {
                string[] expressions = dict["Expressions"].Split(',');
                foreach (string expressionField in expressions)
                {
                    if (dictFields.ContainsKey(expressionField))
                    {
                        string expressionCode = "" + dictFields[expressionField];
                        var expVal = TwiExpression.GetExpressionValueByCode(expressionCode);
                        dictFields[expressionField] = expVal == null ? "" : expVal;
                    }
                }
            }
            
            Dictionary<string, dynamic> varDict = new Dictionary<string, dynamic>();
            varDict.Add("tableName", tableName);
            varDict.Add("dictUpdateFields", dictFields);
            varDict.Add("dictConditionFields", dictCondition);
            varDict.Add("dictFnFields", dictFnFields);
            SimpleEdit(varDict);
        }

        /// <summary>
        /// 简单编辑操作
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dictUpdateFields">要修改的字段及其值字典</param>
        /// <param name="dictConditionFields">简单条件字典（所谓简单，就是条件都是 = 和 and 连接）</param>
        public void SimpleEdit(string tableName, Dictionary<string, object> dictUpdateFields, Dictionary<string, object> dictConditionFields)
        {
            Dictionary<string, dynamic> varDict = new Dictionary<string, dynamic>();
            varDict.Add("tableName", tableName);
            varDict.Add("dictUpdateFields", dictUpdateFields);
            varDict.Add("dictConditionFields", dictConditionFields);
            SimpleEdit(varDict);
        }


        /// <summary>
        /// 简单编辑操作
        /// </summary>
        /// <param name="varDict">
        ///   参数说明:
        ///   tableName:表名 【必填】
        ///   Dictionary<string, object> dictUpdateFields:要修改的字段及其值字典 【必填】
        ///   Dictionary<string, object> dictConditionFields:简单条件字典（所谓简单，就是条件都是 = 和 and 连接） 【必填】
        ///   Dictionary<string, string> dictFnFields = null:为采用数据库函数取值字段配置集合【可选】
        /// </param>
        public void SimpleEdit(Dictionary<string, dynamic> varDict) 
        {
            //参数
            string tableName = varDict["tableName"];
            Dictionary<string, object> dictUpdateFields = varDict["dictUpdateFields"];
            Dictionary<string, object> dictConditionFields = varDict["dictConditionFields"];
            Dictionary<string, string> dictFnFields = varDict.SelfOrDefault("dictFnFields", null);

            string updateFields = "";
            if (dictFnFields != null && dictFnFields.Count > 0)
            {
                foreach (KeyValuePair<string, string> kvp in dictFnFields)
                {
                    updateFields += "," + string.Format("{0}={1}", kvp.Key, kvp.Value);
                }
            }
            foreach (string key in dictUpdateFields.Keys)
            {
                updateFields += "," + string.Format("{0}={1}{0}", key, _ParameterPrefix);
            }
            updateFields = updateFields.Substring(1);
            string conditionFields = "";
            foreach (string key in dictConditionFields.Keys)
            {
                conditionFields += " and " + string.Format("{0}={1}{0}", key, _ParameterPrefix);
            }
            conditionFields = conditionFields.Substring(4); //去掉最前面的一个and
            string strSql = string.Format("update {0} set {1} where {2}", tableName, updateFields, conditionFields);
            var mergeDicts = TwiDictionaryHelper<string>.MergeTwoDicts(dictUpdateFields, dictConditionFields);
            var dbParams = DB.CreateDbParameter(mergeDicts, "");
            DB.ExecuteNonQuery(strSql, dbParams);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="id">ID的值(如果有多个，用逗号隔开)</param>
        /// <param name="idField">ID字段（default:FID）</param>
        public void Delete(string tableName, string id, string idField = "FID")
        {
            tableName = GetTableName(tableName);
            string[] ids = id.Split(',');
            Delete(tableName, ids, idField);
            //string strSql = string.Format("delete from {0} where {1}={2}id", tableName,idField,_ParameterPrefix);
            //List<DbParameter> para = new List<DbParameter> { DB.CreateDbParameter("id", id) };
            //DB.ExecuteNonQuery(strSql, para);
        }

        /// <summary>
        /// 多项删除(事务)
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ids">FIDs</param>
        /// <param name="idField">ID字段（default:FID）</param>
        public void Delete(string tableName, string[] ids, string idField = "FID")
        {
            tableName = GetTableName(tableName);
            int tmpID = 0;
            List<TransMultiPara> listTmp = new List<TransMultiPara>();
            foreach (string id in ids)
            {
                string strSql = string.Format("delete from {0} where {1}={2}id", tableName, idField, _ParameterPrefix);
                List<DbParameter> parameters = new List<DbParameter>()
                {
                    DB.CreateDbParameter("id",id)
                };
                TransMultiPara tmp = new TransMultiPara();
                tmp.ID = ++tmpID;
                tmp.cmdText = strSql.ToString();
                tmp.cmdParams = parameters;
                listTmp.Add(tmp);
            }
            DB.ExecuteSqlTransMultiPara(listTmp);
        }

        /// <summary>
        /// 根据删除状态删除数据（事务）
        /// </summary>
        /// <param name="data">JSON</param>
        /// <example>
        /// json编辑格式：
        /// {
        ///     TableName:"TDuty",
        ///     IDs:'ID1,ID2',
        ///     IDField:'FID', //默认FID
        ///     DeleteField : "FIsDeleted", //默认FIsDeleted
        ///     Value:"1" //默认 1
        /// }
        /// </example>
        public void DeleteBySetFlag(string data)
        {
            var dict = JSSerialize.Deserialize<dynamic>(data);
            string tableName = "" + dict["TableName"];
            tableName = GetTableName(tableName);
            string[] ids = ("" + dict["IDs"]).Split(',');
            string idField = dict.ContainsKey("IDField") ? "" + dict["IDField"] : "FID";
            string deleteField = dict.ContainsKey("DeleteField") ? "" + dict["DeleteField"] : "FIsDeleted";
            string value = dict.ContainsKey("Value") ? "" + dict["Value"] : "1";
            DeleteBySetFlag(tableName, ids, idField, deleteField, value);
        }

        /// <summary>
        /// 根据删除状态删除数据（事务）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ids">IDs</param>
        /// <param name="idField">ID字段名</param>
        /// <param name="deleteField">删除状态字段名</param>
        /// <param name="value">删除状态设置的值</param>
        public void DeleteBySetFlag(string tableName, string[] ids, string idField = "FID", string deleteField = "FIsDeleted", string value = "1")
        {
            int tmpID = 0;
            List<TransMultiPara> listTmp = new List<TransMultiPara>();
            foreach (string id in ids)
            {
                string strSql = string.Format("update {0} set {1}={2}{1} where {3}={2}{3}", tableName, deleteField, _ParameterPrefix, idField);
                List<DbParameter> parameters = new List<DbParameter>()
                {
                    DB.CreateDbParameter(deleteField,value),
                    DB.CreateDbParameter(idField,id),
                };
                TransMultiPara tmp = new TransMultiPara();
                tmp.ID = ++tmpID;
                tmp.cmdText = strSql.ToString();
                tmp.cmdParams = parameters;
                listTmp.Add(tmp);
            }
            DB.ExecuteSqlTransMultiPara(listTmp);
        }

        /// <summary>
        /// 新增或编辑
        /// </summary>
        /// <param name="data">约定的json格式(新增时，IDField不传，则默认为FID)</param>
        /// <remarks>
        /// 由于参数是直接从web从传递的所以为了保证安全性，对该方法进行了如下限制：
        /// 1、该方法仅支持简单的新增和编辑操作，Conditon表示编辑的条件，逻辑关系为“=”，多条件为“and”连接
        /// 2、对应复杂的组合条件，大家可以自己写方法。
        /// 3、SortModel为启用智能排序功能，如果没有该项则不需要
        /// </remarks>
        /// <example>
        /// json新增格式：
        /// {
        ///     TableName:"TDuty",
        ///     NewOrEdit:"New", 
        ///     IDField:'FID',
        ///     Fields:{
        ///         FName:"开发人员",
        ///         FSort:"8"
        ///     },
        ///     SortModel: {   
        ///        SortField:'FSort',
        ///        SortValue:'',
        ///        FilterField:'TOptionType',
        ///        FilterValue:''
        ///     }    
        /// }
        /// json编辑格式：
        /// {
        ///     TableName:"TDuty",
        ///     NewOrEdit:"Edit",
        ///     Fields:{
        ///         FName:"开发人员",
        ///         FSort:"8"
        ///     },
        ///     Condition:{
        ///         FID:""
        ///     },
        ///     SortModel: {   
        ///        SortField:'FSort',
        ///        SortValue:'',
        ///        FilterField:'TOptionTypeID',
        ///        FilterValue:''
        ///     },
        ///     FnFields:'FID,FDate',
        ///     MaxCodeFields:'FID,FCode',
        ///     DateFields:'FBeginDate,FEndDate'
        /// }
        /// 对新增操作，如果没有指定FID，则会自动生成。
        /// </example>
        /// <remarks>
        ///    FnFields 为采用数据库函数取值字段配置集合,
        ///    MaxCodeFields 为从最大记录编号表中取值的字段集合
        /// </remarks>
        /// <returns>返回FID</returns>
        public string NewOrEdit(string data)
        {
            string returnFID = null;
            var dict = JSSerialize.Deserialize<dynamic>(data);
            var dictFields = dict["Fields"];
            string tableName = "" + dict["TableName"];
            tableName = GetTableName(tableName);
            string idfield = "FID";
            if (dict.ContainsKey("IDField"))
            {
                idfield = "" + dict["IDField"];
            }
            Dictionary<string, string> dictFnFields = null;
            if (dict.ContainsKey("FnFields"))
            {
                string[] fnFields = ("" + dict["FnFields"]).Split(',');
                dictFnFields = new Dictionary<string, string>();
                for (int i = 0; i < fnFields.Length; i++)
                {
                    if (dictFields.ContainsKey(fnFields[i]))
                    {
                        dictFnFields.Add(fnFields[i], "" + dictFields[fnFields[i]]);
                        dictFields.Remove(fnFields[i]);
                    }
                }
            }
            //if (dict.ContainsKey("Condition"))//编辑操作
            string newOrEdit = "" + dict["NewOrEdit"];
            if (newOrEdit == "Edit")
            {
                var dictCondition = dict["Condition"];
                string updateFields = "";
                if (dictFnFields != null && dictFnFields.Count > 0)
                {
                    foreach (KeyValuePair<string, string> kvp in dictFnFields)
                    {
                        updateFields += "," + string.Format("{0}={1}", kvp.Key, kvp.Value);
                    }
                }
                foreach (string key in dictFields.Keys)
                {
                    updateFields += "," + string.Format("{0}={1}{0}", key, _ParameterPrefix);
                }
                updateFields = updateFields.Substring(1);
                string conditionFields = "";
                foreach (string key in dictCondition.Keys)
                {
                    conditionFields += " and " + string.Format("{0}={1}{0}", key, _ParameterPrefix);
                }
                string strSql = string.Format("update {0} set {1} where 1=1 {2}", tableName, updateFields, conditionFields);
                Dictionary<string, object> mergeDicts = TwiDictionaryHelper<string>.MergeTwoDicts(dictFields, dictCondition);
                IList<DbParameter> dbParams = DB.CreateDbParameter(mergeDicts, "");
                if (dict.ContainsKey("DateFields"))
                {
                    string[] dateFields = dict["DateFields"].Split(',');
                    TwiDAHelper.ListDbParamsForDate(ref dbParams, dateFields);
                }
                DB.ExecuteNonQuery(strSql, dbParams);

                if (dictCondition.ContainsKey(idfield))
                {
                    returnFID = dictCondition[idfield];
                }
            }
            else//新增操作
            {
                if (!dictFields.ContainsKey(idfield))
                {
                    dictFields.Add(idfield, "" + Guid.NewGuid());
                }
                else
                {
                    dictFields[idfield] = dictFields[idfield] == "" ? "" + Guid.NewGuid() : dictFields[idfield];
                }
                returnFID = "" + dictFields[idfield];
                Dictionary<string, string> dictMaxCodeFields = null;
                if (dict.ContainsKey("MaxCodeFields"))
                {
                    string[] maxCodeFields = ("" + dict["MaxCodeFields"]).Split(',');
                    dictMaxCodeFields = new Dictionary<string, string>();
                    for (int i = 0; i < maxCodeFields.Length; i++)
                    {
                        dictMaxCodeFields.Add(maxCodeFields[i], "" + dictFields[maxCodeFields[i]]);
                        dictFields.Remove(maxCodeFields[i]);
                    }
                }
                string fields = TwiDictionaryHelper<string>.GetDictionaryKeys(dictFields, ",");
                string fieldParams = _ParameterPrefix + TwiDictionaryHelper<string>.GetDictionaryKeys(dictFields, "," + _ParameterPrefix);
                string fnFieldKeys = "";
                string fnFieldValues = "";
                if (dictFnFields != null && dictFnFields.Count > 0)
                {
                    foreach (KeyValuePair<string, string> kvp in dictFnFields)
                    {
                        fnFieldKeys += "," + kvp.Key;
                        fnFieldValues += "," + kvp.Value;
                    }
                    fields = fields.Length == 0 ? fnFieldKeys.Substring(1) : fields + fnFieldKeys;
                    fieldParams = fieldParams.Length == 0 ? fnFieldValues.Substring(1) : fieldParams + fnFieldValues;
                }
                string maxCodeFieldsKeys = "";
                string maxCodeFieldValues = "";
                int nextMaxCode = 0;
                if (dictMaxCodeFields != null && dictMaxCodeFields.Count > 0)
                {
                    foreach (KeyValuePair<string, string> kvp in dictMaxCodeFields)
                    {
                        maxCodeFieldsKeys += "," + kvp.Key;
                        nextMaxCode = MaxCode.GetNextValue(kvp.Value);
                        maxCodeFieldValues += string.Format(",'{0}'", nextMaxCode);
                    }
                    fields = fields.Length == 0 ? maxCodeFieldsKeys.Substring(1) : fields + maxCodeFieldsKeys;
                    fieldParams = fieldParams.Length == 0 ? maxCodeFieldValues.Substring(1) : fieldParams + maxCodeFieldValues;
                    returnFID = nextMaxCode > 0 ? "" + nextMaxCode : returnFID;
                }
                string strSql = string.Format("insert into {0} ({1}) values ({2})", tableName, fields, fieldParams);
                IList<DbParameter> dbParams = DB.CreateDbParameter(dictFields, "");
                if (dict.ContainsKey("DateFields"))
                {
                    string[] dateFields = dict["DateFields"].Split(',');
                    TwiDAHelper.ListDbParamsForDate(ref dbParams, dateFields);
                }
                DB.ExecuteNonQuery(strSql, dbParams);
            }

            //执行智能生成排序字段
            if (dict.ContainsKey("SortModel"))
            {
                var dictSort = dict["SortModel"];
                SortModel model = new SortModel();
                model.TableName = tableName;
                model.NewOrEdit = newOrEdit;
                model.IDField = idfield;
                model.IDValue = returnFID;
                if (dictSort.ContainsKey("SortField"))
                {
                    model.SortField = dictSort["SortField"];
                }                
                if (dictSort.ContainsKey("SortValue"))
                {
                    model.SortValue = dictSort["SortValue"];
                }
                else
                {
                    model.SortValue = dictFields[model.SortField.ToUpper()];
                }

                if (dictSort.ContainsKey("FilterField"))
                {
                    model.FilterField = dictSort["FilterField"];

                    if (dictSort.ContainsKey("FilterValue"))
                    {
                        model.FilterValue = dictSort["FilterValue"];
                    }
                    else
                    {
                        model.FilterValue = dictFields[model.FilterField.ToUpper()];
                    }
                }
                AutoGenSort(model);
            }
            return returnFID;
        }


        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="selectFields">查询列默认所有*</param>
        /// <returns></returns>
        public DataTable GetAll(string tableName, string selectFields = "*", string orderFields = null)
        {
            tableName = GetTableName(tableName);
            CheckSafe.Validate(selectFields);
            string strSql = string.Format("select {0} from {1}", selectFields, tableName);
            if (!string.IsNullOrEmpty(orderFields))
            {
                CheckSafe.Validate(orderFields);
                strSql += " order by " + orderFields;
            }
            return DB.ExecuteDataTable(strSql);
        }

        /// <summary>
        /// 根据ID获取记录
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="id">ID值</param>
        /// <param name="idField">ID字段名（default:FID）</param>
        /// <param name="selectFields">查询字段（default:*）</param>
        /// <param name="needMappingTable">是否需要表映射</param>
        /// <returns></returns>
        public DataTable GetByID(string tableName, string id, string idField = "FID", string selectFields = "*", bool needMappingTable = true)
        {
            tableName = needMappingTable ? GetTableName(tableName) : tableName;
            CheckSafe.Validate(selectFields);
            string strSql = string.Format("select {0} from {1} where {2}={3}FID", selectFields, tableName, idField, _ParameterPrefix);
            List<DbParameter> listParas = new List<DbParameter>();
            listParas.Add(DB.CreateDbParameter("FID", id));
            return DB.ExecuteDataTable(strSql, listParas);
        }

        /// <summary>
        /// 根据ID获取第一行第一列的值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="id">ID值</param>
        /// <param name="idField">ID字段名（default:FID）</param>
        /// <param name="selectFields">查询字段（default:*）</param>
        /// <param name="needMappingTable">是否需要表映射</param>
        /// <returns></returns>
        public object GetScalarByID(string tableName, string id, string idField = "FID", string selectFields = "*", bool needMappingTable = true)
        {
            tableName = needMappingTable ? GetTableName(tableName) : tableName;
            CheckSafe.Validate(selectFields);
            string strSql = string.Format("select {0} from {1} where {2}={3}FID", selectFields, tableName, idField, _ParameterPrefix);
            List<DbParameter> listParas = new List<DbParameter>();
            listParas.Add(DB.CreateDbParameter("FID", id));
            object obj = DB.ExecuteScalar(strSql, listParas);
            return obj;
        }

        /// <summary>
        /// 简单条件查询
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="data">json对象</param>
        /// <example>
        /// json格式：
        /// {
        ///     TableName:"TDuty",
        ///     IsTMConfig:1,
        ///     TMFile:'', //可不填
        ///     SelectFields:"FID,FName,FSort", (如果不传，则默认查询所有*)
        ///     OrderFields:'',//不传则表示不排序
        ///     Condition:{
        ///         FID:""
        ///     },
        ///     SimpleCondition:{
        ///         Field:'FName',
        ///         Value:'志',
        ///         COperator:'Like'
        ///     },
        ///     TreeCondition:{
        ///         Field:'FDepartmentTCode',
        ///         Value:'101'
        ///     },
        ///     ColNameToUpper:true, //列名是否需要转换为大写
        ///     EasyUITreeGrid:{
        ///        ParentIDField:'FPARENTID',
        ///        RootParentIDValue:''
        ///     }
        /// }
        /// </example>
        /// <remarks>可选参数EasyUITreeGrid:目的是为配置EasyUI专属的_parentId列</remarks>
        public DataTable GetBySimpleCondition(string data)
        {
            CheckSafe.Validate(data);
            var dict = JSSerialize.Deserialize<dynamic>(data);
            bool isTMConfig = false;
            if (dict.ContainsKey("IsTMConfig"))
            {
                isTMConfig = Convert.ToBoolean(dict["IsTMConfig"]);
            }
            string tableName = "" + dict["TableName"];
            string tmFile = null;
            if (dict.ContainsKey("TMFile")) {
                tmFile = "" + dict["TMFile"];
                tmFile = TableMappingFullPath.Substring(0, TableMappingFullPath.LastIndexOf('\\') + 1) + tmFile;
            }
            tableName = GetTableName(tableName, isTMConfig, tmFile);
            string selectFields = dict.ContainsKey("SelectFields") ? "" + dict["SelectFields"] : "*";
            var dictCondition = dict.ContainsKey("Condition") ? dict["Condition"] : null;
            string orderFields = dict.ContainsKey("OrderFields") ? "" + dict["OrderFields"] : null;

            SimpleCondition simpleCondition = null;
            if (dict.ContainsKey("SimpleCondition"))
            {
                Dictionary<string, object> dictSimple = (Dictionary<string, object>)dict["SimpleCondition"];
                CompareOperator cOperator = (CompareOperator)Enum.Parse(typeof(CompareOperator), "" + dictSimple["COperator"], false);
                simpleCondition = new SimpleCondition();
                simpleCondition.Field = "" + dictSimple["Field"];
                simpleCondition.Value = "" + dictSimple["Value"];
                if (dictSimple.ContainsKey("DataType"))
                {
                    simpleCondition.DataType = TwiEnumHelper.GetEnumByNameOrValue<DbType>("" + dictSimple["DataType"]);                    
                }
                simpleCondition.COperator = cOperator;
            }

            SimpleCondition treeCondition = null;
            if (dict.ContainsKey("TreeCondition"))
            {
                Dictionary<string, object> dictSimple = (Dictionary<string, object>)dict["TreeCondition"];
                treeCondition = new SimpleCondition();
                treeCondition.Field = "" + dictSimple["Field"];
                treeCondition.Value = "" + dictSimple["Value"];
                treeCondition.COperator = CompareOperator.StartWith; //树节点在前台就不用配置这个属性了
            }

            DataTable dt = GetBySimpleCondition(tableName, dictCondition, simpleCondition, selectFields, orderFields, treeCondition);
            if (dict.ContainsKey("ColNameToUpper") && Convert.ToBoolean(dict["ColNameToUpper"]))
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.ToUpper();
                }
            }
            if (dict.ContainsKey("EasyUITreeGrid"))
            {
                Dictionary<string, object> dictTreeGrid = dict["EasyUITreeGrid"];
                string parentIDField = dictTreeGrid.ContainsKey("ParentIDField") ? "" + dictTreeGrid["ParentIDField"] : "FPARENTID";
                string rootParentIDValue = dictTreeGrid.ContainsKey("RootParentIDValue") ? "" + dictTreeGrid["RootParentIDValue"] : "";
                if (dt.Columns.Contains(parentIDField))
                {
                    dt.Columns.Add("_parentId", typeof(string));
                    string parentId = "";
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        parentId = "" + dt.Rows[i][parentIDField];
                        dt.Rows[i]["_parentId"] = parentId == rootParentIDValue ? "" : parentId;
                    }

                }
            }
            return dt;
        }

        /// <summary>
        /// 简单条件查询,
        /// </summary>
        /// <param name="tableName">表名/视图名</param>
        /// <param name="dictCondition">简单条件字典（所谓简单，就是单表而且条件都是 = 和 and 连接）</param>
        /// <param name="simpleCondition">附加一个简单条件，一般是非等于的条件，如like</param>
        /// <param name="selectFields">查询列默认所有*</param>
        /// <param name="orderFields">排序如：FSort DESC</param>
        /// <param name="treeCondition">树节点筛选</param>
        /// <returns></returns>
        public DataTable GetBySimpleCondition(string tableName, Dictionary<string, object> dictCondition, SimpleCondition simpleCondition, string selectFields = "*", string orderFields = null, SimpleCondition treeCondition = null)
        {
            string strSql = string.Format("select {0} from {1}", selectFields, tableName);
            string conditionFields = "";
            IList<DbParameter> dbParams = new List<DbParameter>();
            if (dictCondition != null && dictCondition.Count > 0)
            {
                dbParams = DB.CreateDbParameter(dictCondition);
                foreach (string key in dictCondition.Keys)
                {
                    conditionFields += " and " + string.Format("{0}={1}{0}", key, _ParameterPrefix);
                }
            }
            //包含SimpleCondition
            if (simpleCondition != null && !string.IsNullOrEmpty(simpleCondition.Value))
            {                
                if (simpleCondition.COperator == CompareOperator.In)
                {  //xzh 2014-11-12 10:34:54  in做下特殊处理
                    string simpleConSqlItem = TwiConditionHelper.GetSimpleConditionSqlItem(ref simpleCondition, _ParameterPrefix);
                    simpleConSqlItem = simpleConSqlItem.Replace(_ParameterPrefix + simpleCondition.Field, simpleCondition.Value);
                    conditionFields += " and " + simpleConSqlItem;
                }
                else
                {
                    conditionFields += " and " + TwiConditionHelper.GetSimpleConditionSqlItem(ref simpleCondition, _ParameterPrefix);
                    if (simpleCondition.DataType == DbType.Date || simpleCondition.DataType == DbType.DateTime || simpleCondition.DataType == DbType.DateTime2)
                    {
                        //为了解决Oracle语言环境问题产生无效的月份问题
                        dbParams.Add(DB.CreateDbParameter(simpleCondition.Field, Convert.ToDateTime(simpleCondition.Value), simpleCondition.DataType));
                    }
                    else
                    {
                        dbParams.Add(DB.CreateDbParameter(simpleCondition.Field, simpleCondition.Value, simpleCondition.DataType));
                    }
                }
            }

            //包含TreeCondition
            if (treeCondition != null && !string.IsNullOrEmpty(treeCondition.Value))
            {
                conditionFields += " and " + TwiConditionHelper.GetSimpleConditionSqlItem(ref treeCondition, _ParameterPrefix);
                dbParams.Add(DB.CreateDbParameter(treeCondition.Field, treeCondition.Value));
            }

            //条件不为空
            if (!string.IsNullOrEmpty(conditionFields))
            {
                strSql += " where " + conditionFields.Substring(4); //去掉最前面的一个and
            }

            if (!string.IsNullOrEmpty(orderFields))
            {
                strSql += " order by " + orderFields;
            }

            return DB.ExecuteDataTable(strSql, dbParams);
        }

        /// <summary>
        /// 简单条件分页查询
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="data">json对象</param>
        /// <param name="paging">PagingCondition对象</param>
        /// <example>
        /// json格式：
        /// {
        ///     TableName:"TDuty",
        ///     IsTMConfig:"0", 
        ///     TMFile:'', //可不填
        ///     SelectFields:"FID,FName,FSort", (如果不传，则默认查询所有*)
        ///     OrderFields:'',//不传则表示不排序 SQL Server一定要传
        ///     Condition:{
        ///         FID:""
        ///     },
        ///     SimpleCondition:{
        ///         Field:'FName',
        ///         Value:'志',
        ///         COperator:'Like'
        ///     },
        ///     TreeCondition:{
        ///         Field:'FDepartmentTCode',
        ///         Value:'101'
        ///     },
        ///     RDACondition:{                   //RDA: RoleDataAuthority 角色数据权限
        ///         DeptID:'Sys_Dept.DeptID',    //参数名为要控制的字段，参数值为bim_roledataauthority
        ///         ParkCode:'Sys_Park.ParkCode'
        ///     },
        ///     ColNameToUpper:true, //列名是否需要转换为大写
        /// }
        /// </example>
        public DataTable GetPagingDataByCondition(string data, PagingCondition paging = null)
        {
            CheckSafe.Validate(data);
            var dict = JSSerialize.Deserialize<dynamic>(data);
            bool isTMConfig = false;
            if (dict.ContainsKey("IsTMConfig"))
            {
                isTMConfig = Convert.ToBoolean(dict["IsTMConfig"]);
            }
            string tableName = "" + dict["TableName"];
            string tmFile = null;
            if (dict.ContainsKey("TMFile"))
            {
                tmFile = "" + dict["TMFile"];
                tmFile = TableMappingFullPath.Substring(0, TableMappingFullPath.LastIndexOf('\\') + 1) + tmFile;
            }
            tableName = GetTableName(tableName, isTMConfig, tmFile);

            string selectFields = dict.ContainsKey("SelectFields") ? "" + dict["SelectFields"] : "*";
            var dictCondition = dict.ContainsKey("Condition") ? dict["Condition"] : null;
            //角色数据权限
            Dictionary<string, object> rdaCondition = dict.ContainsKey("RDACondition") ? dict["RDACondition"] : null;
            string orderFields = dict.ContainsKey("OrderFields") ? "" + dict["OrderFields"] : null;
            SimpleCondition simpleCondition = null;
            if (dict.ContainsKey("SimpleCondition"))
            {
                Dictionary<string, object> dictSimple = (Dictionary<string, object>)dict["SimpleCondition"];
                CompareOperator cOperator = (CompareOperator)Enum.Parse(typeof(CompareOperator), "" + dictSimple["COperator"], false);
                simpleCondition = new SimpleCondition();
                simpleCondition.Field = "" + dictSimple["Field"];
                simpleCondition.Value = "" + dictSimple["Value"];
                simpleCondition.COperator = cOperator;
            }
            SimpleCondition treeCondition = null;
            if (dict.ContainsKey("TreeCondition"))
            {
                Dictionary<string, object> dictSimple = (Dictionary<string, object>)dict["TreeCondition"];
                treeCondition = new SimpleCondition();
                treeCondition.Field = "" + dictSimple["Field"];
                treeCondition.Value = "" + dictSimple["Value"];
                treeCondition.COperator = CompareOperator.StartWith; //树节点在前台就不用配置这个属性了
            }
            //var simpleCondition = dict.ContainsKey("SimpleCondition") ? JSSerialize.Deserialize<SimpleCondition>(dict["SimpleCondition"]) : null;
            DataTable dt = GetPagingDataByCondition(tableName, dictCondition, simpleCondition, selectFields, orderFields, paging, treeCondition,rdaCondition);
            if (dict.ContainsKey("ColNameToUpper") && Convert.ToBoolean(dict["ColNameToUpper"]))
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.ToUpper();
                }
            }
            return dt;
        }

        /// <summary>
        /// 简单条件分页查询,
        /// </summary>
        /// <param name="tableName">表名/视图名</param>
        /// <param name="dictCondition">简单条件字典（所谓简单，就是单表而且条件都是 = 和 and 连接）</param>
        /// <param name="simpleCondition">附加一个简单条件，一般是非等于的条件，如like</param>
        /// <param name="selectFields">查询列默认所有*</param>
        /// <param name="orderFields">排序如：FSort DESC</param>
        /// <param name="paging"></param>
        /// <param name="treeCondition">树节点筛选</param>
        /// <param name="rdaCondition">角色数据权限条件</param>
        /// <returns></returns>
        public DataTable GetPagingDataByCondition(string tableName, Dictionary<string, object> dictCondition, SimpleCondition simpleCondition, string selectFields = "*", string orderFields = null, PagingCondition paging = null, SimpleCondition treeCondition = null,Dictionary<string, object> rdaCondition=null)
        {
            string strSql = "";
            string conditionFields = "";
            IList<DbParameter> dbParams = new List<DbParameter>();
            IList<DbParameter> totalCountParams = new List<DbParameter>();

            if (dictCondition != null && dictCondition.Count > 0)
            {
                dbParams = DB.CreateDbParameter(dictCondition);
                totalCountParams = DB.CreateDbParameter(dictCondition);
                foreach (string key in dictCondition.Keys)
                {
                    conditionFields += " and " + string.Format("{0}={1}{0}", key, _ParameterPrefix);
                }
            }

            //角色数据权限
            if (rdaCondition != null && rdaCondition.Count > 0)
            {
                if (TwiDataAuthority.CheckIsOnRight())
                {
                    DataTable dtRda = TwiDataAuthority.GetUserDataAuthority();
                    string rdaSql = "";
                    foreach (string key in rdaCondition.Keys) {                        
                        DataRow[] rowRda = dtRda.Select(string.Format("FFIELDNAME = '{0}'", rdaCondition[key]));
                        string rdaInFormat = TwiIDsHelper.GetInFormatIDs(rowRda, "FFIELDVALUE",true);
                        //数据权限为限制方式，如果一个权限没有，表示拥有全部权限，不做控制
                        if (rdaInFormat != "('')") 
                        {
                            rdaSql += " or " + string.Format("{0} in {1}", key, rdaInFormat);
                        }
                    }
                    if (rdaSql.Length > 4)
                    {
                        rdaSql = rdaSql.Substring(3);
                        rdaSql = string.Format(" and ({0})",rdaSql);
                        conditionFields += rdaSql;
                    }
                }
            }

            //包含SimpleCondition
            if (simpleCondition != null && !string.IsNullOrEmpty(simpleCondition.Value))
            {
                conditionFields += " and " + TwiConditionHelper.GetSimpleConditionSqlItem(ref simpleCondition, _ParameterPrefix);
                dbParams.Add(DB.CreateDbParameter(simpleCondition.Field, simpleCondition.Value));
                totalCountParams.Add(DB.CreateDbParameter(simpleCondition.Field, simpleCondition.Value));
            }

            //包含TreeCondition
            if (treeCondition != null && !string.IsNullOrEmpty(treeCondition.Value))
            {
                conditionFields += " and " + TwiConditionHelper.GetSimpleConditionSqlItem(ref treeCondition, _ParameterPrefix);
                dbParams.Add(DB.CreateDbParameter(treeCondition.Field, treeCondition.Value));
                totalCountParams.Add(DB.CreateDbParameter(treeCondition.Field, treeCondition.Value));
            }

            //条件不为空
            if (!string.IsNullOrEmpty(conditionFields))
            {
                strSql += " where " + conditionFields.Substring(4); //去掉最前面的一个and
            }

            if (paging != null && paging.needPaging)
            {
                if (_DBProviderType == DbProviderType.Oracle || _DBProviderType == DbProviderType.OracleDataAccess)
                {
                    if (!string.IsNullOrEmpty(orderFields))
                    {
                        strSql += " order by " + orderFields;
                    }
                    strSql = string.Format("select {0} from {1} ", selectFields, tableName) + strSql;
                    //oracle 不能一次性执行两条查询，所以只能查两次了  
                    StringBuilder builder = new StringBuilder();
                    builder.Append("select * from (select T.*,RowNum  RN from (" + strSql);
                    builder.Append(")T) where RN <=" + _ParameterPrefix + "endIndex and RN>=" + _ParameterPrefix + "startIndex");

                    //分页参数
                    dbParams.Add(DB.CreateDbParameter("startIndex", paging.startIndex + 1));
                    dbParams.Add(DB.CreateDbParameter("endIndex", paging.startIndex + paging.pageSize));

                    DataTable dt = DB.ExecuteDataTable(builder.ToString(), dbParams);

                    //dbParams 竟然不能共用，另一个 OracleParameterCollection 中已包含 OracleParameter
                    string strSqlTotalCount = "select count(1) from (" + strSql + ")";
                    string totalCount = "" + DB.ExecuteScalar(strSqlTotalCount, totalCountParams);
                    totalCount = totalCount == "" ? "0" : totalCount;

                    dt.TableName = totalCount;
                    return dt;
                }
                else if (_DBProviderType == DbProviderType.SqlServer)
                {
                    if (string.IsNullOrEmpty(orderFields))
                    {
                        throw new Exception("OrderFields未配置，导致分页查询无法排序");
                    }
                    StringBuilder builder = new StringBuilder();
                    builder.Append(string.Format("select count(1) from {0} {1};", tableName, strSql));
                    builder.Append("select * from (");
                    builder.Append(string.Format("select ROW_NUMBER() OVER (ORDER BY {0}) AS Row, {1} from {2} ", orderFields, selectFields, tableName) + strSql);
                    builder.Append(string.Format(") as PagedResults WHERE Row BETWEEN {0} AND {1}", paging.startIndex + 1, paging.startIndex + paging.pageSize));
                    DataSet ds = DB.ExecuteDataSet(builder.ToString(), dbParams);
                    DataTable dt = ds.Tables[1];
                    dt.TableName = "" + ds.Tables[0].Rows[0][0];
                    return dt;
                }
                else
                {
                    throw new Exception(string.Format("目前不支持{0}数据库对通用分页方法的调用！", Enum.GetName(typeof(DbProviderType), _DBProviderType)));
                }
            }
            else
            {
                return DB.ExecuteDataTable(strSql, dbParams);
            }

        }


        /// <summary>
        /// 获取分页数据，一般在已经写好的SQL中要转变成分页的时候使用。
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="dbParams"></param>
        /// <param name="paging"></param>
        /// <returns></returns>
        public DataTable GetPagingData(string strSql, IList<DbParameter> dbParams, PagingCondition paging)
        {
            if (paging != null && paging.needPaging)
            {
                if (_DBProviderType == DbProviderType.Oracle || _DBProviderType == DbProviderType.OracleDataAccess)
                {
                    //dbParams 竟然不能共用，另一个 OracleParameterCollection 中已包含 OracleParameter.
                    //所以先复制一份
                    List<DbParameter> totalCountParams = new List<DbParameter>();
                    dbParams = dbParams == null ? new List<DbParameter>() : dbParams;
                    foreach (DbParameter para in dbParams)
                    {
                        totalCountParams.Add(DB.CreateDbParameter(para.ParameterName, para.Value, para.DbType));
                    }

                    //oracle 不能一次性执行两条查询，所以只能查两次了  
                    StringBuilder builder = new StringBuilder();
                    builder.Append("select * from (select T.*,RowNum  RN from (" + strSql);
                    builder.Append(")T) where RN <=" + _ParameterPrefix + "endIndex and RN>=" + _ParameterPrefix + "startIndex");

                    //分页参数
                    dbParams.Add(DB.CreateDbParameter("startIndex", paging.startIndex + 1));
                    dbParams.Add(DB.CreateDbParameter("endIndex", paging.startIndex + paging.pageSize));

                    DataTable dt = DB.ExecuteDataTable(builder.ToString(), dbParams);

                    string strSqlTotalCount = "select count(1) from (" + strSql + ")";
                    string totalCount = "" + DB.ExecuteScalar(strSqlTotalCount, totalCountParams);
                    totalCount = totalCount == "" ? "0" : totalCount;

                    dt.TableName = totalCount;
                    return dt;
                }
                else if (_DBProviderType == DbProviderType.SqlServer)
                {
                    if (string.IsNullOrEmpty(paging.orderByFields))
                    {
                        throw new Exception("OrderFields未配置，导致分页查询无法排序");
                    }
                    StringBuilder builder = new StringBuilder();
                    builder.Append(string.Format("select count(1) from ({0}) as AllRecord;", strSql));
                    builder.Append("select * from (");
                    builder.Append(string.Format("select ROW_NUMBER() OVER (ORDER BY {0}) AS Row, * from ({1}) as AllRecord ", paging.orderByFields, strSql));
                    builder.Append(string.Format(") as PagedResults WHERE Row BETWEEN {0} AND {1}", paging.startIndex + 1, paging.startIndex + paging.pageSize));
                    DataSet ds = DB.ExecuteDataSet(builder.ToString(), dbParams);
                    DataTable dt = ds.Tables[1];
                    dt.TableName = "" + ds.Tables[0].Rows[0][0];
                    return dt;
                }
                else
                {
                    throw new Exception(string.Format("目前不支持{0}数据库对通用分页方法的调用！", Enum.GetName(typeof(DbProviderType), _DBProviderType)));
                }
            }
            else
            {
                DataTable dt = DB.ExecuteDataTable(strSql, dbParams);
                dt.TableName = "" + dt.Rows.Count;
                return dt;
            }
        }

        /// <summary>
        /// 条件查询【分页】
        /// </summary>
        /// <param name="start">跳过系列中指定的条数[Extjs中的start]</param>
        /// <param name="limit">每页显示条数[Extjs中的limit]</param>
        /// <param name="twiData">前台发给服务器端的JSON字符串</param>
        /// <remarks>
        /// Condition:对象配置等于条件（=）
        /// Query：对象配置模糊查询条件（like）
        /// </remarks>
        /// <returns></returns>
        public DataTable GetPagingDataByCondition(int start, int limit, string twiData)
        {
            CheckSafe.Validate(twiData);
            Dictionary<string, object> dict = TwiJsonHelper.JsonToDictionary2(twiData);
            string tableName = "" + dict["TableName"];
            tableName = GetTableName(tableName);
            string orderBy = "" + dict["OrderBy"];
            //等于条件（=）
            Dictionary<string, object> dictCondition = TwiJsonHelper.JsonToDictionary2("" + dict["Condition"]);
            string conditionFields = "";
            foreach (string key in dictCondition.Keys)
            {
                conditionFields += " and " + string.Format("{0}={1}{0}", key, _ParameterPrefix);
            }
            //模糊查询（like）
            Dictionary<string, object> dictQuery = TwiJsonHelper.JsonToDictionary2("" + dict["Query"]);
            string queryFields = "";
            foreach (string key in dictQuery.Keys)
            {
                queryFields += " and " + string.Format("{0} like '%{0}%'", key); //这里直接赋值，不使用参数形式
            }
            string strWhere = " where 1=1 " + conditionFields + queryFields;
            //var mergeDicts = TwiDictionaryHelper<string>.MergeTwoDicts(dictCondition, dictQuery);
            //var dbParams = DB.CreateDbParameter(mergeDicts, "");
            var dbParams = DB.CreateDbParameter(dictCondition);
            dbParams.Add(DB.CreateDbParameter("startIndex", start + 1, DbType.Int32));
            dbParams.Add(DB.CreateDbParameter("endIndex", start + limit, DbType.Int32));

            if (_DBProviderType == DbProviderType.SqlServer)
            {
                StringBuilder builder = new StringBuilder();

                builder.Append(" select count(1) from " + tableName + " ");
                builder.Append(strWhere);
                builder.Append(";");
                builder.Append(" select * from ");
                builder.Append(" (select *,row_number() over(order by " + orderBy + ") as RowNumber from " + tableName + " ");
                builder.Append(strWhere);
                builder.Append(" )PageResult");
                builder.Append(" where RowNumber between @startIndex  and @endIndex ");

                DataSet ds = DB.ExecuteDataSet(builder.ToString(), dbParams);
                DataTable dt = ds.Tables[1];
                dt.TableName = "" + ds.Tables[0].Rows[0][0];
                return dt;
            }
            else if (_DBProviderType == DbProviderType.Oracle || _DBProviderType == DbProviderType.OracleDataAccess)
            {
                StringBuilder builder = new StringBuilder();
                builder.Append(" select count(1) from " + tableName + " ");
                builder.Append(strWhere);
                builder.Append(";");
                builder.Append("select * from (select T.*,RowNum  RN from (");
                builder.Append("select * from  " + tableName + " ");
                builder.Append(strWhere);
                builder.Append(" order by " + orderBy);
                builder.Append(")T where RN <=:endIndex) where RN>=:startIndex");

                DataSet ds = DB.ExecuteDataSet(builder.ToString(), dbParams);
                DataTable dt = ds.Tables[1];
                dt.TableName = "" + ds.Tables[0].Rows[0][0];
                return dt;
            }
            else
            {
                throw new Exception(string.Format("目前不支持{0}数据库对通用分页方法的调用！", Enum.GetName(typeof(DbProviderType), _DBProviderType)));
            }
        }

        /// <summary>
        /// 根据SQL-MAP查询数据
        /// </summary>
        /// <param name="data">json对象</param>
        /// <example>
        /// json参数格式：
        /// {
        ///     Code:"001",
        ///     Parameters:{
        ///         FID:"",
        ///         FName:''
        ///     },
        ///     SQLMapFile:''
        /// }
        /// </example>
        /// <returns></returns>
        public DataTable GetBySQLMap(string data)
        {
            //CheckSafe.Validate(data); //不用安全检查，最后都会是SQL参数化查询
            //var dict = JSSerialize.Deserialize<dynamic>(data);
            //string sqlMapFileName = SQLMapFullPath;
            //if (dict.ContainsKey("SQLMapFile"))
            //{
            //    sqlMapFileName = SQLMapFullPath.Substring(0,SQLMapFullPath.LastIndexOf('\\')+1) + dict["SQLMapFile"];
            //}
            //SQLMapCommandInfo commondInfo = GetSQLMapCommandInfo(dict["Code"], sqlMapFileName);
            //List<DbParameter> listParams = null;
            //if (dict.ContainsKey("Parameters"))
            //{
            //    listParams = commondInfo.GetDbParameters(dict["Parameters"],DB);
            //}
            //return DB.ExecuteDataTable(commondInfo.TransferedSQL, listParams);
            var model = JSSerialize.Deserialize<SQLMapModel>(data);
            return GetBySQLMap(model);
        }

        /// <summary>
        /// 根据SQL-MAP查询数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public DataTable GetBySQLMap(SQLMapModel model)
        {
            if (model == null)
            {
                throw new Exception("参数配置错误，获取到的SQLMapModel为空");
            }
            if (string.IsNullOrEmpty(model.Code))
            {
                throw new Exception("SQLMap唯一标识编号(Code)未配置正确");
            }
            model.SQLMapFile = string.IsNullOrEmpty(model.SQLMapFile) ? SQLMapFullPath : SQLMapFullPath.Substring(0, SQLMapFullPath.LastIndexOf('\\') + 1) + model.SQLMapFile;
            SQLMapCommandInfo commondInfo = GetSQLMapCommandInfo2(model.Code, model.SQLMapFile);
            List<DbParameter> listParams = null;
            if (model.Parameters != null && model.Parameters.Count > 0)
            {
                listParams = commondInfo.GetDbParameters(model.Parameters, DB);
            }
            DataTable dt = DB.ExecuteDataTable(commondInfo.TransferedSQL, listParams);
            if (model.ColNameToUpper)
            {
                dt = TwiDataTableHelper.ColNameToUpper(dt);
            }
            return dt;
        }

        /// <summary>
        /// 根据SQL-MAP查询分页数据
        /// </summary>
        /// <param name="data"></param>
        /// <param name="paging"></param>
        /// <returns></returns>
        public DataTable GetPagingDataBySQLMap(string data, PagingCondition paging = null)
        {
            var model = JSSerialize.Deserialize<SQLMapModel>(data);
            if (model == null)
            {
                throw new Exception("参数配置错误，获取到的SQLMapModel为空");
            }
            if (string.IsNullOrEmpty(model.Code))
            {
                throw new Exception("SQLMap唯一标识编号(Code)未配置正确");
            }
            model.SQLMapFile = string.IsNullOrEmpty(model.SQLMapFile) ? SQLMapFullPath : SQLMapFullPath.Substring(0, SQLMapFullPath.LastIndexOf('\\') + 1) + model.SQLMapFile;
            SQLMapCommandInfo commondInfo = GetSQLMapCommandInfo2(model.Code, model.SQLMapFile);
            List<DbParameter> listParams = null;
            if (model.Parameters != null && model.Parameters.Count > 0)
            {
                listParams = commondInfo.GetDbParameters(model.Parameters, DB);
            }
            DataTable dt = GetPagingData(commondInfo.TransferedSQL, listParams,paging);
            if (model.ColNameToUpper)
            {
                dt = TwiDataTableHelper.ColNameToUpper(dt);
            }
            return dt;
        }

        /// <summary>
        /// 根据SQL-MAP查询数据
        /// </summary>
        /// <param name="data">json对象</param>
        /// <example>
        /// json参数格式：
        /// {
        ///     Code:"001",
        ///     Parameters:{
        ///         FID:"",
        ///         FName:''
        ///     },
        ///     SQLMapFile:''
        /// }
        /// </example>
        /// <returns></returns>
        public XElement GetXmlBySQLMap(string data)
        {
            var model = JSSerialize.Deserialize<SQLMapModel>(data);
            return GetXmlBySQLMap(model);
        }

        /// <summary>
        /// 根据SQL-MAP查询XML数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public XElement GetXmlBySQLMap(SQLMapModel model)
        {
            if (model == null)
            {
                throw new Exception("参数配置错误，获取到的SQLMapModel为空");
            }
            if (string.IsNullOrEmpty(model.Code))
            {
                throw new Exception("SQLMap唯一标识编号(Code)未配置正确");
            }
            model.SQLMapFile = string.IsNullOrEmpty(model.SQLMapFile) ? SQLMapFullPath : SQLMapFullPath.Substring(0, SQLMapFullPath.LastIndexOf('\\') + 1) + model.SQLMapFile;
            SQLMapCommandInfo commondInfo = GetSQLMapCommandInfo2(model.Code, model.SQLMapFile);
            List<DbParameter> listParams = null;
            if (model.Parameters != null && model.Parameters.Count > 0)
            {
                listParams = commondInfo.GetDbParameters(model.Parameters, DB);
            }
                        
            //角色数据权限
            Dictionary<string, object> rdaCondition = model.RDACondition;
            string rdaSql = "";
            if (rdaCondition != null && rdaCondition.Count > 0)
            {
                if (TwiDataAuthority.CheckIsOnRight())
                {
                    DataTable dtRda = TwiDataAuthority.GetUserDataAuthority();                    
                    foreach (string key in rdaCondition.Keys)
                    {
                        DataRow[] rowRda = dtRda.Select(string.Format("FFIELDNAME = '{0}'", rdaCondition[key]));
                        string rdaInFormat = TwiIDsHelper.GetInFormatIDs(rowRda, "FFIELDVALUE", true);
                        //数据权限为限制方式，如果一个权限没有，表示拥有全部权限，不做控制
                        if (rdaInFormat != "('')")
                        {
                            rdaSql += " or " + string.Format("{0} in {1}", key, rdaInFormat);
                        }
                    }
                    if (rdaSql.Length > 4)
                    {
                        rdaSql = rdaSql.Substring(3);
                        rdaSql = string.Format(" where ({0})", rdaSql);
                    }
                }
            }
            string strSql = commondInfo.TransferedSQL;
            if (rdaSql != "") {
                strSql = string.Format("select * from (\r\n{0}\r\n) rda {1}", strSql, rdaSql);
            }
            XElement el = DB.ExecuteXElement(commondInfo.TransferedSQL, listParams);

            return el;
        }

        /// <summary>
        /// 根据SQL-MAP执行SQL
        /// </summary>
        /// <param name="data">json对象</param>
        /// <example>
        /// json参数格式：
        /// {
        ///     Code:"001",
        ///     Parameters:{
        ///         FID:"",
        ///         FName:''
        ///     },
        ///     SQLMapFile:''
        /// }
        /// </example>
        public void ExecuteBySQLMap(string data)
        {
            //CheckSafe.Validate(data); //不用安全检查，最后都会是SQL参数化查询
            //var dict = JSSerialize.Deserialize<dynamic>(data);
            //string sqlMapFileName = SQLMapFullPath;
            //if (dict.ContainsKey("SQLMapFile"))
            //{
            //    sqlMapFileName = SQLMapFullPath.Substring(0,SQLMapFullPath.LastIndexOf('\\')+1) + dict["SQLMapFile"];
            //}
            //var commondInfo = GetSQLMapCommandInfo(dict["Code"],sqlMapFileName);
            //List<DbParameter> listParams = null;
            //if (dict.ContainsKey("Parameters"))
            //{
            //    listParams = commondInfo.GetDbParameters(dict["Parameters"], DB);
            //}
            //DB.ExecuteNonQuery(commondInfo.TransferedSQL, listParams);
            var model = JSSerialize.Deserialize<SQLMapModel>(data);
            ExecuteBySQLMap(model);
        }

        /// <summary>
        /// 根据SQL-MAP执行SQL
        /// </summary>
        /// <param name="model"></param>
        public void ExecuteBySQLMap(SQLMapModel model)
        {
            if (model == null)
            {
                throw new Exception("参数配置错误，获取到的SQLMapModel为空");
            }
            if (string.IsNullOrEmpty(model.Code))
            {
                throw new Exception("SQLMap唯一标识编号(Code)未配置正确");
            }
            model.SQLMapFile = string.IsNullOrEmpty(model.SQLMapFile) ? SQLMapFullPath : SQLMapFullPath.Substring(0, SQLMapFullPath.LastIndexOf('\\') + 1) + model.SQLMapFile;
            SQLMapCommandInfo commondInfo = GetSQLMapCommandInfo2(model.Code, model.SQLMapFile);
            List<DbParameter> listParams = null;
            if (model.Parameters != null && model.Parameters.Count > 0)
            {
                listParams = commondInfo.GetDbParameters(model.Parameters, DB);
            }
            DB.ExecuteNonQuery(commondInfo.TransferedSQL, listParams);
        }

        /// <summary>
        /// 根据传入的data参数获取SQLMapCommandInfo对象
        /// </summary>
        /// <param name="code">SQL-MAP配置中的唯一标识 Code</param>
        /// <param name="sqlMapFileFullPath">完整路径</param>
        /// <returns></returns>
        private SQLMapCommandInfo GetSQLMapCommandInfo2(string code, string sqlMapFileFullPath)
        {
            TwiPathHelper.ExistFile(SQLMapFullPath, true, "未能找到SQLMap配置文件");
            var commondInfo = SQLMapHelper.GetByCode(sqlMapFileFullPath, code, _DBProviderType);
            return commondInfo;
        }

        /// <summary>
        /// 根据传入的data参数获取SQLMapCommandInfo对象
        /// </summary>
        /// <param name="code">SQL-MAP配置中的唯一标识 Code</param>
        /// <returns></returns>
        public SQLMapCommandInfo GetSQLMapCommandInfo(string code)
        {
            return GetSQLMapCommandInfo2(code, SQLMapFullPath);
        }

        /// <summary>
        /// 根据传入的data参数获取SQLMapCommandInfo对象
        /// </summary>
        /// <param name="code">SQL-MAP配置中的唯一标识 Code</param>
        /// <param name="sqlMapShortFileName">仅文件名，非完整路径。如：XzhSQLMap.xml</param>
        /// <returns></returns>
        public SQLMapCommandInfo GetSQLMapCommandInfo(string code,string sqlMapShortFileName)
        {
            string str = string.IsNullOrEmpty(sqlMapShortFileName) ? SQLMapFullPath : SQLMapFullPath.Substring(0, SQLMapFullPath.LastIndexOf('\\') + 1) + sqlMapShortFileName;
            return GetSQLMapCommandInfo2(code, str);
        }

        public XElement SimpleInsertXml(XElement data)
        {
            TwiCommand cmd = DB.CreateCommand();
            XElement xe = new XElement("Root");
            try
            {
                object id = cmd.ExecuteInsert(data);
                xe.SetElementValue("ID", id);
                if (cmd.Transaction != null && cmd.IsAutoSubmitTran)
                {
                    cmd.Transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();
                }
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    if (cmd.DbConnection != null && cmd.DbConnection.State == System.Data.ConnectionState.Open)
                    {
                        cmd.DbConnection.Close();
                        cmd.DbConnection.Dispose();
                    }
                    cmd.Dispose();
                }
            }
            return xe;
        }
        public XElement DeleteXml(XElement data)
        {
            TwiCommand cmd = DB.CreateCommand();
            XElement xe = new XElement("Root");
            try
            {
                string strWhere = " 1=1 ";
                if (!data.HasElements)
                {
                    return xe;
                }
                foreach (XElement xeWhere in data.Elements())
                {
                    if (!string.IsNullOrEmpty(xeWhere.Value))
                    {
                        strWhere += string.Format(@" AND {0} = " + _ParameterPrefix + "{0} ", xeWhere.Name.LocalName);
                        cmd.SetParameter(xeWhere.Name.LocalName, xeWhere.Value);
                    }
                }
                string sql = string.Format(@"DELETE {0} WHERE {1}", data.Name.LocalName, strWhere);
                cmd.CommandText = sql;
                object id = cmd.ExecuteNonQuery();
                xe.SetElementValue("ID", id);
                if (cmd.Transaction != null && cmd.IsAutoSubmitTran)
                {
                    cmd.Transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();
                }
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    if (cmd.DbConnection != null && cmd.DbConnection.State == System.Data.ConnectionState.Open)
                    {
                        cmd.DbConnection.Close();
                        cmd.DbConnection.Dispose();
                    }
                    cmd.Dispose();
                }
            }
            return xe;
        }
        public XElement SimpleUpdateXml(XElement data)
        {
            TwiCommand cmd = DB.CreateCommand();
            XElement xe = new XElement("Root");
            try
            {
                object id = cmd.ExecuteUpdate(data);
                xe.SetElementValue("ID", id);
                if (cmd.Transaction != null && cmd.IsAutoSubmitTran)
                {
                    cmd.Transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();
                }
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    if (cmd.DbConnection != null && cmd.DbConnection.State == System.Data.ConnectionState.Open)
                    {
                        cmd.DbConnection.Close();
                        cmd.DbConnection.Dispose();
                    }
                    cmd.Dispose();
                }
            }
            return xe;
        }

        public XElement GetSingleXml(XElement data)
        {
            TwiCommand cmd = DB.CreateCommand();
            XElement xe = new XElement("Root");
            try
            {
                string strWhere = " 1=1 ";
                if (!data.HasElements)
                {
                    return xe;
                }
                foreach (XElement xeWhere in data.Elements())
                {
                    if (!string.IsNullOrEmpty(xeWhere.Value))
                    {
                        strWhere += string.Format(@" AND {0} = " + _ParameterPrefix + "{0} ", xeWhere.Name.LocalName);
                        cmd.SetParameter(xeWhere.Name.LocalName, xeWhere.Value);
                    }
                }
                string sql = string.Format(@"SELECT * FROM {0} WHERE {1}", data.Name.LocalName, strWhere);
                cmd.CommandText = sql;
                xe = cmd.GetSingleOrDefault(data.Name.LocalName);
                if (cmd.Transaction != null && cmd.IsAutoSubmitTran)
                {
                    cmd.Transaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();
                }
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    if (cmd.DbConnection != null && cmd.DbConnection.State == System.Data.ConnectionState.Open)
                    {
                        cmd.DbConnection.Close();
                        cmd.DbConnection.Dispose();
                    }
                    cmd.Dispose();
                }
            }
            return xe;
        }


        /// <summary>
        /// 是否不需要写日志，等于1时不往数据库中写简单日志（BIM_SIMPLELOG）
        /// </summary>
        private string TwiIgnoreSimpleLog = TwiConfigHelper.GetAppSettingValue("TwiIgnoreSimpleLog");
        /// <summary>
        /// 写简单日志
        /// </summary>
        /// <param name="dict">
        /// FOPTORCODE:'',
        /// FOPTORNAME:'',
        /// FREMARK:'',
        /// FREQUESTDATA:'',
        /// FMN:'',
        /// FFUNCTIONNAME:''
        /// </param>
        public void WriteSimpleLog(Dictionary<string,object> dict)
        {
            if (TwiIgnoreSimpleLog != "1") //写日志
            {
                string tableName = "BIM_SIMPLELOG";
                dict.Add("FID", Guid.NewGuid().ToString());
                dict.Add("FCREATETIME", TwiDateHelper.GetNowDateTime());
                dict.Add("FIP", TwiNetHelper.GetIp());
                SimpleNew(tableName, dict);
            }
        }

        /// <summary>
        /// 获取数据库时间
        /// </summary>
        /// <returns></returns>
        public string GetDBNowDateTime() 
        {
            string strSql = "";
            if (_DBProviderType == DbProviderType.Oracle || _DBProviderType == DbProviderType.OracleDataAccess)
            {
                strSql = "SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS NOWTIME FROM DUAL";
                object obj = DB.ExecuteScalar(strSql);
                return "" + obj;
            }
            else if (_DBProviderType == DbProviderType.SqlServer)
            {
                strSql = "SELECT GETDATE() AS NOWTIME";
                object obj = DB.ExecuteScalar(strSql);
                return "" + obj;
            }
            else
            {
                throw new Exception(string.Format("目前不支持{0}数据库对GetDBDate方法的调用！", Enum.GetName(typeof(DbProviderType), _DBProviderType)));
            }
        }

    }
}
